<?php
namespace Operationtest\Model;

use Zend\Db\Adapter\Adapter;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\TableGateway\AbstractTableGateway;
use Zend\Db\Sql\Select;
use Zend\Db\Sql\Where;

class BudgetTable extends AbstractTableGateway {
    protected $table = 'Budget2';
    
    public function __construct(Adapter $adapter) {
        $this->adapter = $adapter;
        $this->resultSetPrototype = new ResultSet();
        $this->resultSetPrototype->setArrayObjectPrototype(new Budget());
        $this->initialize();
    }
    
    public function getMonthBudget($month = '') {
        if(!$month)
            $month = date('Y-m-01');

        return $this->select(function(Select $select) use($month) {
            $where = new Where();
            $where->equalTo('month', $month);
            $select->where($where);
        });
    }
    
    public function saveBudget($pk, $name, $value) {
        $tabPk = explode('_', $pk);
        $idStore = $tabPk[0];
        $month = $tabPk[1];
        
        $oldBudget = $this->select(function(Select $select) use ($idStore, $month) {
            $where = new Where();
            $where->equalTo('idStore', $idStore);
            $where->AND;
            $where->equalTo('month', $month);
            $select->where($where);
        });
        
        // Update old Budget
        if($oldBudget->count() > 0) {
            $value = str_replace(' ', '', $value);
            $value = str_replace(',', '.', $value);
            $data = array(
                $name => $value,
            );
            $this->update($data, array('idStore' => $idStore, 'month' => $month));
            return $value;
        } 
        // Add new budget
        else {
            switch($name) {
                case 'nbWeek1':
                    $data = array(
                        'idBudget' => '', 
                        'idStore' => $idStore, 
                        'month' => $month, 
                        'nbWeek1' => $value, 
                        'nbWeek2' => 0, 
                        'nbWeek3' => 0, 
                        'nbWeek4' => 0, 
                        'nbWeek5' => 0, 
                        'nbWeek6' => 0, 
                        'totttc' => 0, 
                        'tm' => 0, 
                        'txFpR' => 0, 
                        'txPc' => 0, 
                        'txRp' => 0, 
                        'txA' => 0, 
                        'txAl' => 0, 
                        'txE' => 0);
                    break;
                case 'nbWeek2':
                    $data = array(
                        'idBudget' => '', 
                        'idStore' => $idStore, 
                        'month' => $month, 
                        'nbWeek1' => 0, 
                        'nbWeek2' => $value, 
                        'nbWeek3' => 0, 
                        'nbWeek4' => 0, 
                        'nbWeek5' => 0, 
                        'nbWeek6' => 0, 
                        'totttc' => 0, 
                        'tm' => 0, 
                        'txFpR' => 0, 
                        'txPc' => 0, 
                        'txRp' => 0, 
                        'txA' => 0, 
                        'txAl' => 0, 
                        'txE' => 0);
                    break;
                case 'nbWeek3':
                    $data = array(
                        'idBudget' => '', 
                        'idStore' => $idStore, 
                        'month' => $month, 
                        'nbWeek1' => 0, 
                        'nbWeek2' => 0, 
                        'nbWeek3' => $value, 
                        'nbWeek4' => 0, 
                        'nbWeek5' => 0, 
                        'nbWeek6' => 0, 
                        'totttc' => 0, 
                        'tm' => 0, 
                        'txFpR' => 0, 
                        'txPc' => 0, 
                        'txRp' => 0, 
                        'txA' => 0, 
                        'txAl' => 0, 
                        'txE' => 0);
                    break;
                case 'nbWeek4':
                    $data = array(
                        'idBudget' => '', 
                        'idStore' => $idStore, 
                        'month' => $month, 
                        'nbWeek1' => 0, 
                        'nbWeek2' => 0, 
                        'nbWeek3' => 0, 
                        'nbWeek4' => $value, 
                        'nbWeek5' => 0, 
                        'nbWeek6' => 0, 
                        'totttc' => 0, 
                        'tm' => 0, 
                        'txFpR' => 0, 
                        'txPc' => 0, 
                        'txRp' => 0, 
                        'txA' => 0, 
                        'txAl' => 0, 
                        'txE' => 0);
                    break;
                case 'nbWeek5':
                    $data = array(
                        'idBudget' => '', 
                        'idStore' => $idStore, 
                        'month' => $month, 
                        'nbWeek1' => 0, 
                        'nbWeek2' => 0, 
                        'nbWeek3' => 0, 
                        'nbWeek4' => 0, 
                        'nbWeek5' => $value, 
                        'nbWeek6' => 0, 
                        'totttc' => 0, 
                        'tm' => 0, 
                        'txFpR' => 0, 
                        'txPc' => 0, 
                        'txRp' => 0, 
                        'txA' => 0, 
                        'txAl' => 0, 
                        'txE' => 0);
                    break;
                case 'nbWeek6':
                    $data = array(
                        'idBudget' => '', 
                        'idStore' => $idStore, 
                        'month' => $month, 
                        'nbWeek1' => 0, 
                        'nbWeek2' => 0, 
                        'nbWeek3' => 0, 
                        'nbWeek4' => 0, 
                        'nbWeek5' => 0, 
                        'nbWeek6' => $value, 
                        'totttc' => 0, 
                        'tm' => 0, 
                        'txFpR' => 0, 
                        'txPc' => 0, 
                        'txRp' => 0, 
                        'txA' => 0, 
                        'txAl' => 0, 
                        'txE' => 0);
                    break;
                case 'totttc':
                    $data = array(
                        'idBudget' => '', 
                        'idStore' => $idStore, 
                        'month' => $month, 
                        'nbWeek1' => 0, 
                        'nbWeek2' => 0, 
                        'nbWeek3' => 0, 
                        'nbWeek4' => 0, 
                        'nbWeek5' => 0, 
                        'nbWeek6' => 0, 
                        'totttc' => $value, 
                        'tm' => 0, 
                        'txFpR' => 0, 
                        'txPc' => 0, 
                        'txRp' => 0, 
                        'txA' => 0, 
                        'txAl' => 0, 
                        'txE' => 0);
                    break;
                case 'tm':
                    $data = array(
                        'idBudget' => '', 
                        'idStore' => $idStore, 
                        'month' => $month, 
                        'nbWeek1' => 0, 
                        'nbWeek2' => 0, 
                        'nbWeek3' => 0, 
                        'nbWeek4' => 0, 
                        'nbWeek5' => 0, 
                        'nbWeek6' => 0,  
                        'totttc' => 0, 
                        'tm' => $value, 
                        'txFpR' => 0, 
                        'txPc' => 0, 
                        'txRp' => 0, 
                        'txA' => 0, 
                        'txAl' => 0, 
                        'txE' => 0);
                    break;
                case 'txFpR':
                    $data = array(
                        'idBudget' => '', 
                        'idStore' => $idStore, 
                        'month' => $month, 
                        'nbWeek1' => 0, 
                        'nbWeek2' => 0, 
                        'nbWeek3' => 0, 
                        'nbWeek4' => 0, 
                        'nbWeek5' => 0, 
                        'nbWeek6' => 0,  
                        'totttc' => 0, 
                        'tm' => 0, 
                        'txFpR' => $value, 
                        'txPc' => 0, 
                        'txRp' => 0, 
                        'txA' => 0, 
                        'txAl' => 0, 
                        'txE' => 0);
                    break;
                case 'txPc':
                    $data = array(
                        'idBudget' => '', 
                        'idStore' => $idStore, 
                        'month' => $month, 
                        'nbWeek1' => 0, 
                        'nbWeek2' => 0, 
                        'nbWeek3' => 0, 
                        'nbWeek4' => 0, 
                        'nbWeek5' => 0, 
                        'nbWeek6' => 0,  
                        'totttc' => 0, 
                        'tm' => 0, 
                        'txFpR' => 0, 
                        'txPc' => $value, 
                        'txRp' => 0, 
                        'txA' => 0, 
                        'txAl' => 0, 
                        'txE' => 0);
                    break;
                case 'txRp':
                    $data = array(
                        'idBudget' => '', 
                        'idStore' => $idStore, 
                        'month' => $month, 
                        'nbWeek1' => 0, 
                        'nbWeek2' => 0, 
                        'nbWeek3' => 0, 
                        'nbWeek4' => 0, 
                        'nbWeek5' => 0, 
                        'nbWeek6' => 0,  
                        'totttc' => 0, 
                        'tm' => 0, 
                        'txFpR' => 0, 
                        'txPc' => 0, 
                        'txRp' => $value, 
                        'txA' => 0, 
                        'txAl' => 0, 
                        'txE' => 0);
                    break;
                case 'txA':
                    $data = array(
                        'idBudget' => '', 
                        'idStore' => $idStore, 
                        'month' => $month, 
                        'nbWeek1' => 0, 
                        'nbWeek2' => 0, 
                        'nbWeek3' => 0, 
                        'nbWeek4' => 0, 
                        'nbWeek5' => 0, 
                        'nbWeek6' => 0,  
                        'totttc' => 0, 
                        'tm' => 0, 
                        'txFpR' => 0, 
                        'txPc' => 0, 
                        'txRp' => 0, 
                        'txA' => $value, 
                        'txAl' => 0, 
                        'txE' => 0);
                    break;
                case 'txAl':
                    $data = array(
                        'idBudget' => '', 
                        'idStore' => $idStore, 
                        'month' => $month, 
                        'nbWeek1' => 0, 
                        'nbWeek2' => 0, 
                        'nbWeek3' => 0, 
                        'nbWeek4' => 0, 
                        'nbWeek5' => 0, 
                        'nbWeek6' => 0,  
                        'totttc' => 0, 
                        'tm' => 0, 
                        'txFpR' => 0, 
                        'txPc' => 0, 
                        'txRp' => 0, 
                        'txA' => 0, 
                        'txAl' => $value, 
                        'txE' => 0);
                    break;
                case 'txE':
                    $data = array(
                        'idBudget' => '', 
                        'idStore' => $idStore, 
                        'month' => $month, 
                        'nbWeek1' => 0, 
                        'nbWeek2' => 0, 
                        'nbWeek3' => 0, 
                        'nbWeek4' => 0, 
                        'nbWeek5' => 0, 
                        'nbWeek6' => 0,  
                        'totttc' => 0, 
                        'tm' => 0, 
                        'txFpR' => 0, 
                        'txPc' => 0, 
                        'txRp' => 0, 
                        'txA' => 0, 
                        'txAl' => 0, 
                        'txE' => $value);
                    break;
                default:
                    $data = array(
                        'idBudget' => '', 
                        'idStore' => $idStore, 
                        'month' => $month, 
                        'nbWeek1' => 0, 
                        'nbWeek2' => 0, 
                        'nbWeek3' => 0, 
                        'nbWeek4' => 0, 
                        'nbWeek5' => 0, 
                        'nbWeek6' => 0,  
                        'totttc' => 0, 
                        'tm' => 0, 
                        'txFpR' => 0, 
                        'txPc' => 0, 
                        'txRp' => 0, 
                        'txA' => 0, 
                        'txAl' => 0, 
                        'txE' => 0);
            }
            
            $this->insert($data);
            return $this->lastInsertValue;
        }
    }
    
    public function importBudget($idStore, $month, $data) {
        $data['idBudget'] = '';
        $data['idStore'] = $idStore;
        $data['month'] = $month;
        
        $this->insert($data);
        return $this->lastInsertValue;
    }
    
    public function getBudgetForTableauDeBord($tbWhere) {
        return $this->select(function(Select $select) use($tbWhere) {
            $select->columns(array('month','nbWeek1','nbWeek2','nbWeek3','nbWeek4','nbWeek5','nbWeek6','totttc','tm','txA','txAl','txE'));
            $select->join(
                    'store',
                    'groupmentCode = Budget2.idStore',
                    array('idStore')
            );
            
            $where = new Where();
            $where->in('store.idStore', $tbWhere['stores']);
            $yearCount = 1;
            foreach($tbWhere['date'] as $idxy => $year) {
                $yearQuery = explode(',', $idxy);
                if($yearCount == 1) {
                    $begin = '((';
                } elseif($yearCount > 1) {
                    $begin = '(';
                    $where->or;
                }
                $where->literal($begin.$yearQuery[0].' = '.$yearQuery[1]);
                
                $countMonth = 1;
                foreach($year as $idxm => $month) {
                    $monthQuery = explode(',', $month);
                    if($countMonth == 1) {
                        $begin = '(';
                        $end = '';
                    } 
                    if($countMonth > 1 && $countMonth < count($year)) {
                        $begin = '';
                        $end = '';
                        $where->or;
                    } 
                    if($countMonth == count($year) && $yearCount < count($tbWhere['date'])) {
                        $begin = '';
                        $end = ')';
                        if(count($year) > 1) {
                            $where->or;
                            $end = '))';
                        }
                    } 
                    if($countMonth == count($year) && $yearCount == count($tbWhere['date'])) {
                        $begin = '';
                        $end = '))';
                        if(count($year) > 1) {
                            $where->or;
                            $end = ')))';
                        }
                        
                    } 
                    
                    $where->literal($begin.$monthQuery[0].' = '.$monthQuery[1].$end);
                    $countMonth++;
                }
                $yearCount++;
            }
            
            $select->where($where);
            
            //echo '<pre>'.@$select->getSqlString().'</pre>';
        });
    }
    
    public function getBudgetForPilotage($bgWhere) {
        return $this->select(function (Select $select) use($bgWhere) {
            $select->columns(array('month','nbWeek1','nbWeek2','nbWeek3','nbWeek4','nbWeek5','nbWeek6','totttc','tm','txFpR','txPc','txRp','txA','txAl','txE'));
            $select->join(
                    'store',
                    'groupmentCode = Budget2.idStore',
                    array('idStore')
            );     
            
            $where = new Where();
            $where->in('store.idStore', $bgWhere['stores']);
            $yearCount = 1;
            foreach($bgWhere['date'] as $idxy => $year) {
                $yearQuery = explode(',', $idxy);
                if($yearCount == 1) {
                    $begin = '((';
                } elseif($yearCount > 1) {
                    $begin = '(';
                    $where->or;
                }
                $where->literal($begin.$yearQuery[0].' = '.$yearQuery[1]);
                
                $countMonth = 1;
                foreach($year as $idxm => $month) {
                    $monthQuery = explode(',', $month);
                    if($countMonth == 1) {
                        $begin = '(';
                        $end = '';
                    } 
                    if($countMonth > 1 && $countMonth < count($year)) {
                        $begin = '';
                        $end = '';
                        $where->or;
                    } 
                    if($countMonth == count($year) && $yearCount < count($bgWhere['date'])) {
                        $begin = '';
                        $end = ')';
                        if(count($year) > 1) {
                            $where->or;
                            $end = '))';
                        }
                    } 
                    if($countMonth == count($year) && $yearCount == count($bgWhere['date'])) {
                        $begin = '';
                        $end = '))';
                        if(count($year) > 1) {
                            $where->or;
                            $end = ')))';
                        }
                        
                    } 
                    
                    $where->literal($begin.$monthQuery[0].' = '.$monthQuery[1].$end);
                    $countMonth++;
                }
                $yearCount++;
            }
            
            $select->where($where);
            
            //echo '<pre>'.@$select->getSqlString().'</pre>';
        });
    }
    
    public function getMarcheBudgetForPilotage($marcheBgWhere) {
        if($marcheBgWhere['withMarche'] == 1) {
            return $this->select(function(Select $select) use($marcheBgWhere){
                $select->columns(array('month','nbWeek1','nbWeek2','nbWeek3','nbWeek4','nbWeek5','nbWeek6','totttc','tm','txFpR','txPc','txRp','txA','txAl','txE'));
                $select->join(
                        'store',
                        'groupmentCode = Budget2.idStore',
                        array('idStore')
                );
                $select->join(
                        'MarcheStore',
                        'MarcheStore.idStore = store.idStore',
                        array('idMarche')
                );

                $where = new Where();
                //$where->in('MarcheStore.idStore', $marcheBgWhere['stores']);
                $where->in('MarcheStore.idMarche', $marcheBgWhere['marches']);


                $yearCount = 1;
                foreach($marcheBgWhere['date'] as $idxy => $year) {
                    $yearQuery = explode(',', $idxy);
                    if($yearCount == 1) {
                        $begin = '((';
                    } elseif($yearCount > 1) {
                        $begin = '(';
                        $where->or;
                    }
                    $where->literal($begin.$yearQuery[0].' = '.$yearQuery[1]);

                    $countMonth = 1;
                    foreach($year as $idxm => $month) {
                        $monthQuery = explode(',', $month);
                        if($countMonth == 1) {
                            $begin = '(';
                            $end = '';
                        } 
                        if($countMonth > 1 && $countMonth < count($year)) {
                            $begin = '';
                            $end = '';
                            $where->or;
                        } 
                        if($countMonth == count($year) && $yearCount < count($marcheBgWhere['date'])) {
                            $begin = '';
                            $end = ')';
                            if(count($year) > 1) {
                                $where->or;
                                $end = '))';
                            }
                        } 
                        if($countMonth == count($year) && $yearCount == count($marcheBgWhere['date'])) {
                            $begin = '';
                            $end = '))';
                            if(count($year) > 1) {
                                $where->or;
                                $end = ')))';
                            }

                        } 

                        $where->literal($begin.$monthQuery[0].' = '.$monthQuery[1].$end);
                        $countMonth++;
                    }
                    $yearCount++;
                }

                $select->where($where);

                //echo '<pre>'.@$select->getSqlString().'</pre>';
            });
        } else {
            return false;
        }
    }
}